<?php

class Sale extends CI_Model {

    public function get_info($sale_id) {
        $this->db->from('sales');
        $this->db->where('sale_id', $sale_id);
        return $this->db->get();
    }

    function exists($sale_id) {
        $this->db->from('sales');
        $this->db->where('sale_id', $sale_id);
        $query = $this->db->get();

        return ($query->num_rows() == 1);
    }

    function update($sale_data, $sale_id) {
        $this->db->where('sale_id', $sale_id);
        $success = $this->db->update('sales', $sale_data);

        return $success;
    }

    function save($items, $customer_id, $employee_id, $comment, $payments, $sale_id = false) {
        if (count($items) == 0)
            return -1;

        $payment_types = '';
        foreach ($payments as $payment_id => $payment) {
            $payment_types = $payment_types . $payment['payment_type'] . ': ' . to_currency($payment['payment_amount']) . '<br />';
        }

        $sales_data = array(
            'sale_time' => date('Y-m-d H:i:s'),
            'customer_id' => $this->Customer->exists($customer_id) ? $customer_id : null,
            'employee_id' => $employee_id,
            'payment_type' => $payment_types,
            'comment' => $comment
        );

        //Run these queries as a transaction, we want to make sure we do all or nothing
        $this->db->trans_start();

        $this->db->insert('sales', $sales_data);
        $sale_id = $this->db->insert_id();

        foreach ($payments as $payment_id => $payment) {
            if (substr($payment['payment_type'], 0, strlen(lang('sales_giftcard'))) == lang('sales_giftcard')) {
                /* We have a gift card and we have to deduct the used value from the total value of the card. */
                $splitpayment = explode(':', $payment['payment_type']);
                $cur_giftcard_value = $this->Giftcard->get_giftcard_value($splitpayment[1]);
                $this->Giftcard->update_giftcard_value($splitpayment[1], $cur_giftcard_value - $payment['payment_amount']);
            }

            $sales_payments_data = array
                (
                'sale_id' => $sale_id,
                'payment_type' => $payment['payment_type'],
                'payment_amount' => $payment['payment_amount']
            );
            $this->db->insert('sales_payments', $sales_payments_data);
        }

        foreach ($items as $line => $item) {
            if (isset($item['item_id'])) {
                $cur_item_info = $this->Item->get_info($item['item_id']);

                $sales_items_data = array
                    (
                    'sale_id' => $sale_id,
                    'item_id' => $item['item_id'],
                    'line' => $item['line'],
                    'description' => $item['description'],
                    'serialnumber' => $item['serialnumber'],
                    'quantity_purchased' => $item['quantity'],
                    'discount_percent' => $item['discount'],
                    'item_cost_price' => $cur_item_info->cost_price,
                    'item_unit_price' => $item['price']
                );

                $this->db->insert('sales_items', $sales_items_data);

                //Update stock quantity
                $item_data = array('quantity' => $cur_item_info->quantity - $item['quantity']);
                $this->Item->save($item_data, $item['item_id']);

                //Ramel Inventory Tracking
                //Inventory Count Details
                $qty_buy = -$item['quantity'];
                $sale_remarks = 'POS ' . $sale_id;
                $inv_data = array
                    (
                    'trans_date' => date('Y-m-d H:i:s'),
                    'trans_items' => $item['item_id'],
                    'trans_user' => $employee_id,
                    'trans_comment' => $sale_remarks,
                    'trans_inventory' => $qty_buy
                );
                $this->Inventory->insert($inv_data);
            } else {
                $cur_item_kit_info = $this->Item_kit->get_info($item['item_kit_id']);

                $sales_item_kits_data = array
                    (
                    'sale_id' => $sale_id,
                    'item_kit_id' => $item['item_kit_id'],
                    'line' => $item['line'],
                    'description' => $item['description'],
                    'quantity_purchased' => $item['quantity'],
                    'discount_percent' => $item['discount'],
                    'item_kit_cost_price' => $cur_item_kit_info->cost_price,
                    'item_kit_unit_price' => $item['price']
                );

                $this->db->insert('sales_item_kits', $sales_item_kits_data);

                foreach ($this->Item_kit_items->get_info($item['item_kit_id']) as $item_kit_item) {
                    $cur_item_info = $this->Item->get_info($item_kit_item->item_id);

                    //Update stock quantity
                    $item_data = array('quantity' => $cur_item_info->quantity - ($item['quantity'] * $item_kit_item->quantity));
                    $this->Item->save($item_data, $item_kit_item->item_id);

                    //Ramel Inventory Tracking
                    //Inventory Count Details
                    $qty_buy = -$item['quantity'] * $item_kit_item->quantity;
                    $sale_remarks = 'POS ' . $sale_id;
                    $inv_data = array
                        (
                        'trans_date' => date('Y-m-d H:i:s'),
                        'trans_items' => $item_kit_item->item_id,
                        'trans_user' => $employee_id,
                        'trans_comment' => $sale_remarks,
                        'trans_inventory' => $qty_buy
                    );
                    $this->Inventory->insert($inv_data);
                }
            }

            $customer = $this->Customer->get_info($customer_id);
            if ($customer_id == -1 or $customer->taxable) {
                if (isset($item['item_id'])) {
                    foreach ($this->Item_taxes->get_info($item['item_id']) as $row) {
                        $this->db->insert('sales_items_taxes', array(
                            'sale_id' => $sale_id,
                            'item_id' => $item['item_id'],
                            'line' => $item['line'],
                            'name' => $row['name'],
                            'percent' => $row['percent'],
                            'cumulative' => $row['cumulative']
                        ));
                    }
                } else {
                    foreach ($this->Item_kit_taxes->get_info($item['item_kit_id']) as $row) {
                        $this->db->insert('sales_item_kits_taxes', array(
                            'sale_id' => $sale_id,
                            'item_kit_id' => $item['item_kit_id'],
                            'line' => $item['line'],
                            'name' => $row['name'],
                            'percent' => $row['percent'],
                            'cumulative' => $row['cumulative']
                        ));
                    }
                }
            }
        }
        $this->db->trans_complete();

        if ($this->db->trans_status() === FALSE) {
            return -1;
        }

        return $sale_id;
    }

    function delete($sale_id) {
        $employee_id = $this->Employee->get_logged_in_employee_info()->person_id;

        $this->db->select('item_id, quantity_purchased');
        $this->db->from('sales_items');
        $this->db->where('sale_id', $sale_id);

        foreach ($this->db->get()->result_array() as $sale_item_row) {
            $cur_item_info = $this->Item->get_info($sale_item_row['item_id']);
            $item_data = array('quantity' => $cur_item_info->quantity + $sale_item_row['quantity_purchased']);
            $this->Item->save($item_data, $sale_item_row['item_id']);

            $sale_remarks = 'POS ' . $sale_id;
            $inv_data = array
                (
                'trans_date' => date('Y-m-d H:i:s'),
                'trans_items' => $sale_item_row['item_id'],
                'trans_user' => $employee_id,
                'trans_comment' => $sale_remarks,
                'trans_inventory' => $sale_item_row['quantity_purchased']
            );
            $this->Inventory->insert($inv_data);
        }

        $this->db->select('item_kit_id, quantity_purchased');
        $this->db->from('sales_item_kits');
        $this->db->where('sale_id', $sale_id);

        foreach ($this->db->get()->result_array() as $sale_item_kit_row) {
            foreach ($this->Item_kit_items->get_info($sale_item_kit_row['item_kit_id']) as $item_kit_item) {
                $cur_item_info = $this->Item->get_info($item_kit_item->item_id);

                $item_data = array('quantity' => $cur_item_info->quantity + ($sale_item_kit_row['quantity_purchased'] * $item_kit_item->quantity));
                $this->Item->save($item_data, $item_kit_item->item_id);

                $sale_remarks = 'POS ' . $sale_id;
                $inv_data = array
                    (
                    'trans_date' => date('Y-m-d H:i:s'),
                    'trans_items' => $item_kit_item->item_id,
                    'trans_user' => $employee_id,
                    'trans_comment' => $sale_remarks,
                    'trans_inventory' => $sale_item_kit_row['quantity_purchased'] * $item_kit_item->quantity
                );
                $this->Inventory->insert($inv_data);
            }
        }

        $this->db->where('sale_id', $sale_id);
        return $this->db->update('sales', array('deleted' => 1));
    }

    function undelete($sale_id) {
        $employee_id = $this->Employee->get_logged_in_employee_info()->person_id;

        $this->db->select('item_id, quantity_purchased');
        $this->db->from('sales_items');
        $this->db->where('sale_id', $sale_id);

        foreach ($this->db->get()->result_array() as $sale_item_row) {
            $cur_item_info = $this->Item->get_info($sale_item_row['item_id']);
            $item_data = array('quantity' => $cur_item_info->quantity - $sale_item_row['quantity_purchased']);
            $this->Item->save($item_data, $sale_item_row['item_id']);

            $sale_remarks = 'POS ' . $sale_id;
            $inv_data = array
                (
                'trans_date' => date('Y-m-d H:i:s'),
                'trans_items' => $sale_item_row['item_id'],
                'trans_user' => $employee_id,
                'trans_comment' => $sale_remarks,
                'trans_inventory' => -$sale_item_row['quantity_purchased']
            );
            $this->Inventory->insert($inv_data);
        }

        $this->db->select('item_kit_id, quantity_purchased');
        $this->db->from('sales_item_kits');
        $this->db->where('sale_id', $sale_id);

        foreach ($this->db->get()->result_array() as $sale_item_kit_row) {
            foreach ($this->Item_kit_items->get_info($sale_item_kit_row['item_kit_id']) as $item_kit_item) {
                $cur_item_info = $this->Item->get_info($item_kit_item->item_id);

                $item_data = array('quantity' => $cur_item_info->quantity - ($sale_item_kit_row['quantity_purchased'] * $item_kit_item->quantity));
                $this->Item->save($item_data, $item_kit_item->item_id);

                $sale_remarks = 'POS ' . $sale_id;
                $inv_data = array
                    (
                    'trans_date' => date('Y-m-d H:i:s'),
                    'trans_items' => $item_kit_item->item_id,
                    'trans_user' => $employee_id,
                    'trans_comment' => $sale_remarks,
                    'trans_inventory' => -$sale_item_kit_row['quantity_purchased'] * $item_kit_item->quantity
                );
                $this->Inventory->insert($inv_data);
            }
        }

        $this->db->where('sale_id', $sale_id);
        return $this->db->update('sales', array('deleted' => 0));
    }

    function get_sale_items($sale_id) {
        $this->db->from('sales_items');
        $this->db->where('sale_id', $sale_id);
        return $this->db->get();
    }

    function get_sale_item_kits($sale_id) {
        $this->db->from('sales_item_kits');
        $this->db->where('sale_id', $sale_id);
        return $this->db->get();
    }

    function get_sale_items_taxes($sale_id) {
        $query = $this->db->query('SELECT name, percent, cumulative, item_unit_price as price, quantity_purchased as quantity, discount_percent as discount ' .
                'FROM ' . $this->db->dbprefix('sales_items_taxes') . ' JOIN ' .
                $this->db->dbprefix('sales_items') . ' USING (sale_id, item_id, line) WHERE ' . $this->db->dbprefix('sales_items_taxes') . ".sale_id = $sale_id");
        return $query->result_array();
    }

    function get_sale_item_kits_taxes($sale_id) {
        $query = $this->db->query('SELECT name, percent, cumulative, item_kit_unit_price as price, quantity_purchased as quantity, discount_percent as discount ' .
                'FROM ' . $this->db->dbprefix('sales_item_kits_taxes') . ' JOIN ' .
                $this->db->dbprefix('sales_item_kits') . ' USING (sale_id, item_kit_id, line) WHERE ' . $this->db->dbprefix('sales_item_kits_taxes') . ".sale_id = $sale_id");
        return $query->result_array();
    }

    function get_sale_payments($sale_id) {
        $this->db->from('sales_payments');
        $this->db->where('sale_id', $sale_id);
        return $this->db->get();
    }

    function get_customer($sale_id) {
        $this->db->from('sales');
        $this->db->where('sale_id', $sale_id);
        return $this->Customer->get_info($this->db->get()->row()->customer_id);
    }

    

    //We create a temp table that allows us to do easy report/sales queries
    public function create_sales_items_temp_table($params) {
        $where = '';

        if (isset($params['start_date']) && isset($params['end_date'])) {
            $where = 'WHERE date(sale_time) BETWEEN "' . $params['start_date'] . '" and "' . $params['end_date'] . '"';
        }

        $this->db->query("CREATE TEMPORARY TABLE " . $this->db->dbprefix('sales_items_temp') . "
		(SELECT " . $this->db->dbprefix('sales') . ".deleted as deleted, date(sale_time) as sale_date, " . $this->db->dbprefix('sales_items') . ".sale_id, comment,payment_type, customer_id, employee_id, 
		" . $this->db->dbprefix('items') . ".item_id, NULL as item_kit_id, supplier_id, quantity_purchased, item_cost_price, item_unit_price, category, 
		SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END) as item_tax_percent_regular,
		SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END) as item_tax_percent_cumulative,
		discount_percent, (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) as subtotal,
		" . $this->db->dbprefix('sales_items') . ".line as line, serialnumber, " . $this->db->dbprefix('sales_items') . ".description as description,
		ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)+ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) 
		+((ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100))
		*(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100),2) as total,
		ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) 
		+((ROUND((item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) + (item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100))
		*(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as tax,
		(item_unit_price*quantity_purchased-item_unit_price*quantity_purchased*discount_percent/100) - (item_cost_price*quantity_purchased) as profit
		FROM " . $this->db->dbprefix('sales_items') . "
		INNER JOIN " . $this->db->dbprefix('sales') . " ON  " . $this->db->dbprefix('sales_items') . '.sale_id=' . $this->db->dbprefix('sales') . '.sale_id' . "
		INNER JOIN " . $this->db->dbprefix('items') . " ON  " . $this->db->dbprefix('sales_items') . '.item_id=' . $this->db->dbprefix('items') . '.item_id' . "
		LEFT OUTER JOIN " . $this->db->dbprefix('suppliers') . " ON  " . $this->db->dbprefix('items') . '.supplier_id=' . $this->db->dbprefix('suppliers') . '.person_id' . "
		LEFT OUTER JOIN " . $this->db->dbprefix('sales_items_taxes') . " ON  "
                . $this->db->dbprefix('sales_items') . '.sale_id=' . $this->db->dbprefix('sales_items_taxes') . '.sale_id' . " and "
                . $this->db->dbprefix('sales_items') . '.item_id=' . $this->db->dbprefix('sales_items_taxes') . '.item_id' . " and "
                . $this->db->dbprefix('sales_items') . '.line=' . $this->db->dbprefix('sales_items_taxes') . '.line' . "
		$where
		GROUP BY sale_id, item_id, line) 
		UNION ALL
		(SELECT " . $this->db->dbprefix('sales') . ".deleted as deleted, date(sale_time) as sale_date, " . $this->db->dbprefix('sales_item_kits') . ".sale_id, comment,payment_type, customer_id, employee_id, 
		NULL as item_id, " . $this->db->dbprefix('item_kits') . ".item_kit_id, '' as supplier_id, quantity_purchased, item_kit_cost_price, item_kit_unit_price, category, 
		SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END) as item_kit_tax_percent_regular,
		SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END) as item_kit_tax_percent_cumulative,
		discount_percent, (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100) as subtotal,
		" . $this->db->dbprefix('sales_item_kits') . ".line as line, '' as serialnumber, " . $this->db->dbprefix('sales_item_kits') . ".description as description,
		ROUND((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)+ROUND((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) 
		+((ROUND((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) + (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100))
		*(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100),2) as total,
		ROUND((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) 
		+((ROUND((item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100)*(SUM(CASE WHEN cumulative != 1 THEN percent ELSE 0 END)/100),2) + (item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100))
		*(SUM(CASE WHEN cumulative = 1 THEN percent ELSE 0 END))/100) as tax,
		(item_kit_unit_price*quantity_purchased-item_kit_unit_price*quantity_purchased*discount_percent/100) - (item_kit_cost_price*quantity_purchased) as profit
		FROM " . $this->db->dbprefix('sales_item_kits') . "
		INNER JOIN " . $this->db->dbprefix('sales') . " ON  " . $this->db->dbprefix('sales_item_kits') . '.sale_id=' . $this->db->dbprefix('sales') . '.sale_id' . "
		INNER JOIN " . $this->db->dbprefix('item_kits') . " ON  " . $this->db->dbprefix('sales_item_kits') . '.item_kit_id=' . $this->db->dbprefix('item_kits') . '.item_kit_id' . "
		LEFT OUTER JOIN " . $this->db->dbprefix('sales_item_kits_taxes') . " ON  "
                . $this->db->dbprefix('sales_item_kits') . '.sale_id=' . $this->db->dbprefix('sales_item_kits_taxes') . '.sale_id' . " and "
                . $this->db->dbprefix('sales_item_kits') . '.item_kit_id=' . $this->db->dbprefix('sales_item_kits_taxes') . '.item_kit_id' . " and "
                . $this->db->dbprefix('sales_item_kits') . '.line=' . $this->db->dbprefix('sales_item_kits_taxes') . '.line' . "
		$where
		GROUP BY sale_id, item_kit_id, line) ORDER BY sale_id, line");
       

    }

    public function get_giftcard_value($giftcardNumber) {
        if (!$this->Giftcard->exists($this->Giftcard->get_giftcard_id($giftcardNumber)))
            return 0;

        $this->db->from('giftcards');
        $this->db->where('giftcard_number', $giftcardNumber);
        return $this->db->get()->row()->value;
    }

}

?>
